package com.takeaway.utils;

import com.takeaway.utils.myconnectionpool.MyDataBaseImpl;
import com.takeaway.utils.myconnectionpool.PoolAgent;

import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/**
 * @author 后台赖骏
 * @version 1.0
 */
public class JdbcUtils {
    public static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();
    //1.使用properties将连接获取

    /**
     *
     */
    public static Connection getConnection() throws IOException, SQLException {
        if (THREAD_LOCAL.get() == null){
            Properties properties = new Properties();
            properties.load(new FileInputStream("src/main/java/com/takeaway/config/mysql.properties"));
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            MyDataBaseImpl myDataBase = new MyDataBaseImpl(user, password, url);
            PoolAgent agentConnection = myDataBase.getAgentConnection();
            Connection agent = agentConnection.getAgent();
            THREAD_LOCAL.set(agent);
            return agent;
        }
        else {
            return THREAD_LOCAL.get();
        }


    }

    public static int insert(Object obj) throws Exception{
            Connection connection = JdbcUtils.getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("insert into ");
            Class<?> clz =  obj.getClass();
            String simpleName = clz.getSimpleName();
            sb.append(simpleName).append("(");
            Field[] fields = clz.getDeclaredFields();
            for (Field field : fields) {
                String fieldName = field.getName();
                sb.append(fieldName).append(",");
            }
            sb.deleteCharAt(sb.length() - 1);
            sb.append(") value(null");
            sb.append(",?".repeat(Math.max(0, fields.length - 1)));
            sb.append(");");
            String sql = sb.toString();
            PreparedStatement ps = connection.prepareStatement(sql);
            for (int i = 1; i < fields.length; i++) {
                String fieldName = fields[i].getName();
                String name = "get" + fieldName.toUpperCase().charAt(0)
                        + fieldName.substring(1);
                System.out.println(name);
                Method getObj = clz.getMethod(name);
                Object value = getObj.invoke(obj);
                System.out.println(value);
                ps.setObject(i, value);
            }

            return ps.executeUpdate();

    }

    public static<T> int delete(Class<T> clz, String[] checkFields,Object... factors) throws Exception{
        PreparedStatement ps;
            Connection connection = JdbcUtils.getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("delete from ");
            String simpleName = clz.getSimpleName();
            sb.append(simpleName);
            sb.append(" where ");
            for (String checkField:checkFields) {
                sb.append(checkField).append("= ? and ");
            }
            String sql = sb.substring(0,sb.length()-4);
            System.out.println(sql);
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < factors.length; i++) {
                Object factor = factors[i];
                ps.setObject(i+1,factor);
            }
            return ps.executeUpdate();
    }

    public static int update(Object obj) throws Exception{
        Connection connection ;
        StringBuilder sb = new StringBuilder();
        Class<?> clz = obj.getClass();
        sb.append("update ");
        sb.append(obj.getClass().getSimpleName());
        sb.append(" set ");
        Field[] fields = clz.getDeclaredFields();
        for (int i = 1; i < fields.length; i++) {
            sb.append(fields[i].getName()).append(" = ?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" where id = ?");
        System.out.println(sb);
        Method method ;
            connection = JdbcUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sb.toString());
            for (int i = 0; i < fields.length; i++) {
                String fieldName = fields[i].getName();
                String methodName = "get" + fieldName.toUpperCase().charAt(0)
                        + fieldName.substring(1);
                System.out.println(methodName);
                method = clz.getMethod(methodName);
                Object value = method.invoke(obj);
                System.out.println(value);
                if (i == 0){
                    ps.setObject(fields.length, value);
                }
                else {
                    ps.setObject(i, value);
                }
            }
            return ps.executeUpdate();

    }

    public static Object selectSingle(Object obj, String[] checkFields,Object... factors) throws Exception{
        Connection connection;
        PreparedStatement ps ;
        ResultSet rs;
        StringBuilder sb = new StringBuilder();
        Class<?> clz =  obj.getClass();
        sb.append("select * from ")
                .append(obj.getClass().getSimpleName()).append(" where ");
        for (String checkField:checkFields) {
            sb.append(checkField).append("= ? and ");
        }
        String sql = sb.substring(0,sb.length()-4);
            connection = JdbcUtils.getConnection();
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < factors.length; i++) {
                Object factor = factors[i];
                ps.setObject(i+1,factor);
            }
            rs = ps.executeQuery();
        return getSelect(obj, rs, clz);
    }
    public static<T> List<T> selectMore(Class<T> clz,int begin,int listNum) throws Exception{
        ArrayList<T> list ;
        Connection connection;
        String sql = "select * from " +
                clz.getSimpleName()+" limit ?,?";
            connection = JdbcUtils.getConnection();
            PreparedStatement ps= connection.prepareStatement(sql);
            ps.setInt(1,begin);
            ps.setInt(2,listNum);
            ResultSet rs = ps.executeQuery();
            Method[] methods = clz.getMethods();
            list =new ArrayList<>();
            while(rs.next()){
                T instance = clz.getDeclaredConstructor().newInstance();
                list.add(instance);
                for (Method method: methods) {
                    if (method.getName().contains("set")){
                        Object value = rs.getObject(method.getName().substring(3));
                        method.invoke(instance,value);
                    }
                }
            }


        return list;
    }
    public static<T> List<T> selectMore(Class<T> clz,String[] checkFields,Object... factors) throws Exception{
        ArrayList<T> list ;
        Connection connection ;
        StringBuilder sb = new StringBuilder("select * from " +
                clz.getSimpleName() + " where ");
        for (String checkField:checkFields) {
            sb.append(checkField).append("= ? and ");
        }
        String sql = sb.substring(0,sb.length()-4);
            connection = JdbcUtils.getConnection();
            PreparedStatement ps= connection.prepareStatement(sql);
            for (int i = 0; i < factors.length; i++) {
                Object factor = factors[i];
                ps.setObject(i+1,factor);
            }
            ResultSet rs = ps.executeQuery();
            Method[] methods = clz.getMethods();
            list = new ArrayList<>();
            while(rs.next()){
                T instance = clz.getConstructor().newInstance();
                list.add(instance);
                for (Method method: methods) {
                    if (method.getName().contains("set")){

                        Object value = rs.getObject(method.getName().substring(3));
                        method.invoke(instance,value);
                    }
                }
            }

        return list;
    }
    public static Object executeSelect(Object obj,String sql) throws SQLException, IOException, InvocationTargetException, IllegalAccessException {
        Connection connection;

            connection = JdbcUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            Class<?> clz = obj.getClass();
        return getSelect(obj, rs, clz);
    }

    public static<T> List<T> executeSelectList(Class<T> clz,String sql,Object... params) throws SQLException, IOException, InvocationTargetException, IllegalAccessException, NoSuchMethodException, InstantiationException {
        List<T> list;
        Connection connection;
        connection = JdbcUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            ps.setObject(i+1,params[i]);
        }
        ResultSet rs = ps.executeQuery();
        Method[] methods = clz.getMethods();
        list = new ArrayList<>();
        while(rs.next()){
            T instance = clz.getConstructor().newInstance();
            list.add(instance);
            for (Method method: methods) {
                if (method.getName().contains("set")){

                    Object value = rs.getObject(method.getName().substring(3));
                    method.invoke(instance,value);
                }
            }
        }
        return list;
    }

    public static Object executeSelect(String sql) throws SQLException, IOException{
        Connection connection;
        connection = JdbcUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        rs.next();
        return rs.getObject(1);
    }


    private static Object getSelect(Object obj, ResultSet rs, Class<?> clz) throws SQLException, IllegalAccessException, InvocationTargetException {
        Method[] methods = clz.getMethods();
        if (rs.next()) {
            for (Method method : methods) {
                if (method.getName().contains("set")){
                    Object value = rs.getObject(method.getName().substring(3));
                    method.invoke(obj, value);
                }
            }
        }
        return obj;
    }
}
